
# V appendix  Table A1 admissions by clalit   --------------------------

get_clalit_lookup <- function(con) {
dt_hosp_clalit_lookup <- data.table::as.data.table(
  DBI::dbGetQuery(
    con,
    paste0("sql_query"
    )
  )
)
return(dt_hosp_clalit_lookup)
}


do_Adm_Char_by_Hospital_Ownership <- function(dt_hosp = do_table_A2(top_All_prof_10=top_All_prof_10),
                        dt_hosp_lookup = get_clalit_lookup() ,
                        data = dt_for_exhibits_cancer ) {

dt_hosp_raw_1_row_clalit <- merge( x= dt_hosp, 
                                   y = dt_hosp_lookup, 
                                   by = c("id_var",
                                          "date_start",
                                          "zihui_bikur"),
                                   all.x = T,
                                   all.y = F )



dt_hosp_clalit_raw_test <- merge( x = dt_hosp_raw_1_row_clalit[,-c("prob_for_report","DMG_died_within_365d")], # dt_hosp_clalit_raw,
                                  y = data[,
                                                             .(id_var,
                                                               DMG_age,
                                                               DMG_died_within_365d,
                                                               DMG_gender,
                                                               CHR_NumChrDiagnosesAll,
                                                               ACG_RUB,
                                                               prob_for_report)], 
                                  all.x = F ,
                                  all.y = F , 
                                  by.y  =  "id_var",
                                  by.x = "id_var")


dt_hosp_clalit_stat <- dt_hosp_clalit_raw_test[,.( 'Age (mean, minimum = 25)' = round(mean(DMG_age),1),
                                                   'Sex (% Female)' = round(mean(  DMG_gender == 'F')*100,1) ,
                                                   'Number of Chronic Conditions (mean)' = round(mean(CHR_NumChrDiagnosesAll),1) ,  
                                                   'One-year Mortality (%)' =  round(mean(DMG_died_within_365d == 1 )*100,1),
                                                   'Healthy or Low'     = round( sum(ACG_RUB %in% c("0", "1","2") ) /sum((ACG_RUB != "Missing") )*100,1),
                                                   'Moderate' = round( sum(ACG_RUB %in% c("3") )          /sum((ACG_RUB != "Missing") )*100,1),
                                                   'High or Very High'     = round( sum(ACG_RUB %in% c("4", "5") )     /sum((ACG_RUB != "Missing") )*100,1),
                                                   'High Intensity Admissions (%)' = round( mean( !profession %in% c( 'geriatry' ,'internal_medicine','rehabilitation','oncology'))*100,1),
                                                   'Number of Admissions' = scales::comma(.N) ,
                                                   'Number of Unique Patients' = scales::comma(length(unique(id_var) ) )  

),
by = clalit ]

dt_hosp_pct_t <- 
  data.table(t(dt_hosp_clalit_stat), keep.rownames = T)

library(knitr)
library(kableExtra)
options(knitr.kable.NA = '.')

names(dt_hosp_pct_t) <- c("","(1)", "(2)")

dt_hosp_pct_t[-1,] %>% 
  kable("latex", booktabs = T, align = c("l", rep("r", ncol(.)-1))) %>%
  kable_styling(full_width = F ) %>%
  add_header_above(c("",
                     "Clalit",
                     "Non Clalit"),underline = F,line = F) %>%
  add_header_above(c(" " = 1 , "Hospital Owner" = 2 ), bold = T) %>%
  pack_rows("ACG Score (%)", 5, 7 ) %>%
  row_spec(0, align = "c") %>% 
  write("Admiss_Char_by_Hospital_Ownership.tex")

return( data.table(t(dt_hosp_clalit_stat), keep.rownames = T))
}